﻿using System;
using System.Collections.Generic;
using System.DirectoryServices.ActiveDirectory;
using System.IO;
using System.Text;

namespace DynamicallyBuildUDFBasedOnSchema
{
	class TreeNode : Dictionary<string, TreeNode>, IDisposable
	{
		static string _UDFHeader = buildUDFHeader();

		//List<string> _classes = new List<string>();
		List<string> _inheritingStructuralClasses = new List<string>();
		StreamWriter _output = null;

		ActiveDirectorySchemaClass _schemaClass;
		public ActiveDirectorySchemaClass SchemaClass
		{
			get { return _schemaClass; }
		}

		static string buildUDFHeader()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("using ActiveDirectoryUtilities.CommonLibrary;");
			sb.AppendLine("using ActiveDirectoryUtilities.DirectoryServiceAccess;");
			sb.AppendLine("using Microsoft.SqlServer.Server;");
			sb.AppendLine("using SqlUtilityFunctions;");
			sb.AppendLine("using System;");
			sb.AppendLine("using System.Collections;");
			sb.AppendLine("using System.Data.SqlTypes;");
			sb.AppendLine("using System.Diagnostics;");
			sb.AppendLine("using System.DirectoryServices.Protocols;");
			sb.AppendLine("using System.Globalization;");
			sb.AppendLine("using System.IO;");
			sb.AppendLine("using System.Security.Principal;");
			sb.AppendLine("using System.Xml;");

			sb.AppendLine();
			sb.AppendLine("public partial class UserDefinedFunctions");
			sb.AppendLine("{");
			return sb.ToString();
		}

		internal TreeNode(ActiveDirectorySchemaClass schemaClass)
		{
			_schemaClass = schemaClass;
		}

		new void Add(string className, TreeNode nodes)
		{
			//_classes.Add(className);
			//_classes.Sort();
			base.Add(className, nodes);
		}

		public List<string> CreateUDF()
		{
			foreach (KeyValuePair<string, TreeNode> node in this)
			{
				List<string> childClasses = node.Value.CreateUDF();
				foreach (string s in childClasses)
				{
					if (!_inheritingStructuralClasses.Contains(s))
					{
						_inheritingStructuralClasses.Add(s);
					}
				}
			}

			if (this.SchemaClass.Type == SchemaClassType.Structural)
			{
				_output = new StreamWriter(".\\" + this.SchemaClass.Name + ".cs");

				try
				{
					Console.WriteLine("Creating {0} UDF...", this.SchemaClass.Name);
					_output.Write(_UDFHeader);
					buildSqlFunction();
					_output.WriteLine();
					buildSqlFunctionClass();
					_output.WriteLine();
					buildSqlFunctionPopulator();
					_output.WriteLine("}");

					_output.WriteLine();
					_output.WriteLine();
					_output.WriteLine(string.Format("//{0};{1};{2};{3};{4}", "Property Name", "Property Syntax", "IsSingleValue", "RangeLower", "RangeUpper"));
					foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.MandatoryProperties)
					{
						_output.WriteLine(string.Format("//{0};{1};{2};{3};{4}", prop.Name, prop.Syntax, prop.IsSingleValued, prop.RangeLower, prop.RangeUpper));
						//Console.WriteLine("{0};{1};{2};{3};{4}", prop.Name, prop.Syntax, prop.Syntax, prop.RangeLower, prop.RangeUpper);
					}
					foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.OptionalProperties)
					{
						_output.WriteLine(string.Format("//{0};{1};{2};{3};{4}", prop.Name, prop.Syntax, prop.IsSingleValued, prop.RangeLower, prop.RangeUpper));
						//Console.WriteLine("{0};{1};{2};{3};{4}", prop.Name, prop.Syntax, prop.Syntax, prop.RangeLower, prop.RangeUpper);
					}
				}
				finally
				{
					if (_output != null)
					{
						((IDisposable)_output).Dispose();
					}
				}
			}

			//if (!Program.Compile(true))
			//{
			//	throw new ApplicationException("Exiting because compilation failed.");
			//}

			return _inheritingStructuralClasses;
		}

		private void buildSqlFunction()
		{
			bool IsFirst = true;
			_output.WriteLine("\t[SqlFunction(FillRowMethodName = \"Fill_" + this.SchemaClass.Name + "Row\", DataAccess = DataAccessKind.Read, TableDefinition = ");

			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.MandatoryProperties)
			{
				if (!IsFirst)
				{
					_output.Write(", ");
				}
				else
				{
					_output.WriteLine("\t\t//Mandatory Properties");
					_output.Write("\t\t\"");
				}

				_output.Write(prop.Name);
				_output.Write(" ");
				_output.Write(getDataType(prop));
				if (prop.Name != "nTSecurityDescriptor")
				{
					_output.Write(" NOT NULL");
				}
				else
				{
					_output.Write(" NULL");
				}

				if (prop.Name == "userAccountControl")
				{
					_output.Write(", ADS_UF_SCRIPT BIT, ADS_UF_ACCOUNTDISABLE BIT, ADS_UF_HOMEDIR_REQUIRED BIT, ADS_UF_LOCKOUT BIT, ADS_UF_PASSWD_NOTREQD BIT, ADS_UF_PASSWD_CANT_CHANGE BIT, ADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED BIT, ADS_UF_TEMP_DUPLICATE_ACCOUNT BIT, ADS_UF_NORMAL_ACCOUNT BIT, ADS_UF_INTERDOMAIN_TRUST_ACCOUNT BIT, ADS_UF_WORKSTATION_TRUST_ACCOUNT BIT, ADS_UF_SERVER_TRUST_ACCOUNT BIT, ADS_UF_DONT_EXPIRE_PASSWD BIT, ADS_UF_MNS_LOGON_ACCOUNT BIT, ADS_UF_SMARTCARD_REQUIRED BIT, ADS_UF_TRUSTED_FOR_DELEGATION BIT, ADS_UF_NOT_DELEGATED BIT, ADS_UF_USE_DES_KEY_ONLY BIT, ADS_UF_DONT_REQ_PREAUTH BIT, ADS_UF_PASSWORD_EXPIRED BIT, ADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION BIT, ADS_UF_PARTIAL_SECRETS_ACCOUNT BIT");
				}
				IsFirst = false;
			}
			if (!IsFirst)
			{
				_output.WriteLine("\"");
			}

			IsFirst = true;
			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.OptionalProperties)
			{
				if (!IsFirst)
				{
					_output.Write(", ");
				}
				else
				{
					_output.WriteLine("\t\t//Optional Properties");
					_output.Write("\t\t+ \", ");
				}

				_output.Write(prop.Name);
				_output.Write(" ");
				_output.Write(getDataType(prop));
				_output.Write(" NULL");
				if (prop.Name == "userAccountControl")
				{
					_output.Write(", ADS_UF_SCRIPT BIT, ADS_UF_ACCOUNTDISABLE BIT, ADS_UF_HOMEDIR_REQUIRED BIT, ADS_UF_LOCKOUT BIT, ADS_UF_PASSWD_NOTREQD BIT, ADS_UF_PASSWD_CANT_CHANGE BIT, ADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED BIT, ADS_UF_TEMP_DUPLICATE_ACCOUNT BIT, ADS_UF_NORMAL_ACCOUNT BIT, ADS_UF_INTERDOMAIN_TRUST_ACCOUNT BIT, ADS_UF_WORKSTATION_TRUST_ACCOUNT BIT, ADS_UF_SERVER_TRUST_ACCOUNT BIT, ADS_UF_DONT_EXPIRE_PASSWD BIT, ADS_UF_MNS_LOGON_ACCOUNT BIT, ADS_UF_SMARTCARD_REQUIRED BIT, ADS_UF_TRUSTED_FOR_DELEGATION BIT, ADS_UF_NOT_DELEGATED BIT, ADS_UF_USE_DES_KEY_ONLY BIT, ADS_UF_DONT_REQ_PREAUTH BIT, ADS_UF_PASSWORD_EXPIRED BIT, ADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION BIT, ADS_UF_PARTIAL_SECRETS_ACCOUNT BIT");
				}
				IsFirst = false;
			}
			_output.WriteLine("\"");

			_output.WriteLine("\t)]");
		}

		private void buildSqlFunctionClass()
		{
			_output.Write("\tpublic static IEnumerable fn_Get");
			_output.Write(this.SchemaClass.Name.Replace('-', '_'));
			_output.WriteLine("(SqlString connectionPoint, SqlInt32 portNumber, SqlString baseDn, SqlString filter, SqlString searchScope)");
			_output.WriteLine("\t{");
			_output.WriteLine("\t\tEventLogTraceListener eltl = new EventLogTraceListener(\"Application\");");
			_output.WriteLine("\t\tif (ConfiguredSettings.TraceLevel >= 4)");
			_output.WriteLine("\t\t{");
			_output.WriteLine("\t\t\tTrace.Listeners.Add(eltl);");
			_output.WriteLine("\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\tWindowsImpersonationContext executionContext = null;");
			_output.WriteLine("\t\ttry");
			_output.WriteLine("\t\t{");
			_output.WriteLine("\t\t\tWindowsIdentity callerId = SqlContext.WindowsIdentity;");
			_output.WriteLine("\t\t\tif (callerId != null)");
			_output.WriteLine("\t\t\t{");
			_output.WriteLine("\t\t\t\texecutionContext = callerId.Impersonate();");
			_output.WriteLine();
			_output.WriteLine("\t\t\t\tstring[] attrs = null;");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\t#region Check inputs");
			_output.WriteLine("\t\t\t\tif ((connectionPoint.IsNull) || (connectionPoint.CompareTo(\"\") == 0))");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tthrow new ApplicationException(\"ConnectionPoint parameter can not be null or blank.\");");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\tif (portNumber.IsNull)");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tthrow new ApplicationException(\"PortNumber parameter can not be null.\");");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\tstring internalBaseDn = null;");
			_output.WriteLine("\t\t\t\tif ((baseDn.IsNull) || (baseDn.CompareTo(\"\") == 0))");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tif ((portNumber.Value == (int)LDAP_PORTS.Global_Catalog) || (portNumber.Value == (int)LDAP_PORTS.Secure_Global_Catalog))");
			_output.WriteLine("\t\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\t\tinternalBaseDn = string.Empty;");
			_output.WriteLine("\t\t\t\t\t}");
			_output.WriteLine("\t\t\t\t\telse");
			_output.WriteLine("\t\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\t\tthrow new ApplicationException(\"baseDn parameter can not be null or blank.\");");
			_output.WriteLine("\t\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine("\t\t\t\telse");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tinternalBaseDn = (string)baseDn;");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\tstring internalFilter = filter.IsNull ? string.Empty : (string)filter.Value;");
			_output.WriteLine("\t\t\t\tif (string.IsNullOrEmpty(internalFilter))");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tinternalFilter = \"(objectClass=" + this.SchemaClass.Name + ")\";");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine("\t\t\t\telse if (!internalFilter.ToLowerInvariant().Contains(\"(objectClass=" + this.SchemaClass.Name + "\"))");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tinternalFilter = \"(&(objectClass=" + this.SchemaClass.Name + ")(\" + filter.ToString() + \"))\";");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\tstring internalScopeString = searchScope.IsNull ? string.Empty : (string)searchScope;");
			_output.WriteLine("\t\t\t\tSystem.DirectoryServices.Protocols.SearchScope internalScope;");
			_output.WriteLine("\t\t\t\tswitch (internalScopeString.ToLowerInvariant())");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tcase \"base\":");
			_output.WriteLine("\t\t\t\t\t\tinternalScope = System.DirectoryServices.Protocols.SearchScope.Base;");
			_output.WriteLine("\t\t\t\t\t\tbreak;");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\t\tcase \"onelevel\":");
			_output.WriteLine("\t\t\t\t\t\tinternalScope = System.DirectoryServices.Protocols.SearchScope.OneLevel;");
			_output.WriteLine("\t\t\t\t\t\tbreak;");

			_output.WriteLine("\t\t\t\t\tcase \"subtree\":");
			_output.WriteLine("\t\t\t\t\t\tinternalScope = System.DirectoryServices.Protocols.SearchScope.Subtree;");
			_output.WriteLine("\t\t\t\t\t\tbreak;");

			_output.WriteLine("\t\t\t\t\tcase \"\":");
			_output.WriteLine("\t\t\t\t\t\tinternalScope = System.DirectoryServices.Protocols.SearchScope.Subtree;");
			_output.WriteLine("\t\t\t\t\t\tbreak;");

			_output.WriteLine("\t\t\t\t\tdefault:");
			_output.WriteLine("\t\t\t\t\t\tthrow new ApplicationException(\"Invalid search scope.  Allowed values are Base, OneLevel, Subtree, and NULL.\");");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine("\t\t\t\t#endregion");

			_output.WriteLine();
			_output.WriteLine("\t\t\t\ttry");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tWLdapSearchRequest wsr = new WLdapSearchRequest((string)connectionPoint, (int)portNumber.Value, internalBaseDn, internalFilter, internalScope, attrs);");
			_output.WriteLine("\t\t\t\t\twsr.LdapSearchClientTimeOut = ConfiguredSettings.LdapClientTimeout;");
			_output.WriteLine("\t\t\t\t\treturn wsr.FindAll();");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine("\t\t\t\tcatch (LdapException ex)");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tif (ex.ErrorCode == 81) { throw new ApplicationException(\"Could not contact domain:  \" + (string)connectionPoint);	}");
			_output.WriteLine("\t\t\t\t\telse");
			_output.WriteLine("\t\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\t\tSharedLogic.LogTraceInfo(eltl, ex, TraceLevel.Error);");
			_output.WriteLine("\t\t\t\t\t\tthrow ex;");
			_output.WriteLine("\t\t\t\t\t}");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine("\t\t\t\tcatch (Exception ex)");
			_output.WriteLine("\t\t\t\t{");
			_output.WriteLine("\t\t\t\t\tSharedLogic.LogTraceInfo(eltl, ex, TraceLevel.Error);");
			_output.WriteLine("\t\t\t\t\tthrow ex;");
			_output.WriteLine("\t\t\t\t}");
			_output.WriteLine();

			_output.WriteLine("\t\t\t\tthrow new ApplicationException(\"Invalid codepath traversed.  Caller should never make it here.\");");
			_output.WriteLine("\t\t\t}");
			_output.WriteLine("\t\t}");
			_output.WriteLine("\t\tfinally");
			_output.WriteLine("\t\t{");
			_output.WriteLine("\t\t\tif (Trace.Listeners.Contains(eltl))");
			_output.WriteLine("\t\t\t{");
			_output.WriteLine("\t\t\t\tTrace.Listeners.Remove(eltl);");
			_output.WriteLine("\t\t\t}");
			_output.WriteLine("\t\t\tif (executionContext != null)");
			_output.WriteLine("\t\t\t{");
			_output.WriteLine("\t\t\t\texecutionContext.Undo();");
			_output.WriteLine("\t\t\t}");
			_output.WriteLine("\t\t}");
			_output.WriteLine("\t\treturn null;");
			_output.WriteLine("\t}");
		}

		private void buildSqlFunctionPopulator()
		{
			_output.Write("\tpublic static void Fill" + this.SchemaClass.Name.Replace('-', '_') + "Row(object objectFromDirectory");
			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.MandatoryProperties)
			{
				_output.Write(", out ");
				_output.Write(getSqlDataType(prop));
				_output.Write(" ");
				_output.Write(prop.Name.Replace('-', '_'));

				if (prop.Name == "userAccountControl")
				{
					_output.Write(", out SqlBoolean ADS_UF_SCRIPT, out SqlBoolean ADS_UF_ACCOUNTDISABLE, out SqlBoolean ADS_UF_HOMEDIR_REQUIRED, out SqlBoolean ADS_UF_LOCKOUT, out SqlBoolean ADS_UF_PASSWD_NOTREQD, out SqlBoolean ADS_UF_PASSWD_CANT_CHANGE, out SqlBoolean ADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED, out SqlBoolean ADS_UF_TEMP_DUPLICATE_ACCOUNT, out SqlBoolean ADS_UF_NORMAL_ACCOUNT, out SqlBoolean ADS_UF_INTERDOMAIN_TRUST_ACCOUNT, out SqlBoolean ADS_UF_WORKSTATION_TRUST_ACCOUNT, out SqlBoolean ADS_UF_SERVER_TRUST_ACCOUNT, out SqlBoolean ADS_UF_DONT_EXPIRE_PASSWD, out SqlBoolean ADS_UF_MNS_LOGON_ACCOUNT, out SqlBoolean ADS_UF_SMARTCARD_REQUIRED, out SqlBoolean ADS_UF_TRUSTED_FOR_DELEGATION, out SqlBoolean ADS_UF_NOT_DELEGATED, out SqlBoolean ADS_UF_USE_DES_KEY_ONLY, out SqlBoolean ADS_UF_DONT_REQ_PREAUTH, out SqlBoolean ADS_UF_PASSWORD_EXPIRED, out SqlBoolean ADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION, out SqlBoolean ADS_UF_PARTIAL_SECRETS_ACCOUNT");
				}
			}

			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.OptionalProperties)
			{
				_output.Write(", out ");
				_output.Write(getSqlDataType(prop));
				_output.Write(" ");
				_output.Write(prop.Name.Replace('-', '_'));

				if (prop.Name == "userAccountControl")
				{
					_output.Write(", out SqlBoolean ADS_UF_SCRIPT, out SqlBoolean ADS_UF_ACCOUNTDISABLE, out SqlBoolean ADS_UF_HOMEDIR_REQUIRED, out SqlBoolean ADS_UF_LOCKOUT, out SqlBoolean ADS_UF_PASSWD_NOTREQD, out SqlBoolean ADS_UF_PASSWD_CANT_CHANGE, out SqlBoolean ADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED, out SqlBoolean ADS_UF_TEMP_DUPLICATE_ACCOUNT, out SqlBoolean ADS_UF_NORMAL_ACCOUNT, out SqlBoolean ADS_UF_INTERDOMAIN_TRUST_ACCOUNT, out SqlBoolean ADS_UF_WORKSTATION_TRUST_ACCOUNT, out SqlBoolean ADS_UF_SERVER_TRUST_ACCOUNT, out SqlBoolean ADS_UF_DONT_EXPIRE_PASSWD, out SqlBoolean ADS_UF_MNS_LOGON_ACCOUNT, out SqlBoolean ADS_UF_SMARTCARD_REQUIRED, out SqlBoolean ADS_UF_TRUSTED_FOR_DELEGATION, out SqlBoolean ADS_UF_NOT_DELEGATED, out SqlBoolean ADS_UF_USE_DES_KEY_ONLY, out SqlBoolean ADS_UF_DONT_REQ_PREAUTH, out SqlBoolean ADS_UF_PASSWORD_EXPIRED, out SqlBoolean ADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION, out SqlBoolean ADS_UF_PARTIAL_SECRETS_ACCOUNT");
				}
			}
			_output.WriteLine(")");

			_output.WriteLine("\t{");
			_output.WriteLine("\t\tSearchResultEntry sre = (SearchResultEntry)objectFromDirectory;");
			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.MandatoryProperties)
			{
				_output.Write(populateValueLogic(prop));
			}
			foreach (ActiveDirectorySchemaProperty prop in this.SchemaClass.OptionalProperties)
			{
				_output.Write(populateValueLogic(prop));
			}
			_output.WriteLine("\t}");

		}

		private string populateValueLogic(ActiveDirectorySchemaProperty prop)
		{
			StringBuilder sb = new StringBuilder();

			sb.AppendLine("\t\t//" + prop.Name + " - " + prop.Syntax + " - " + prop.IsSingleValued + " - " + prop.RangeUpper);
			sb.AppendLine("\t\tif (sre.Attributes.Contains(\"" + prop.Name + "\"))");
			sb.AppendLine("\t\t{");
			if (prop.IsSingleValued)
			{
				if (prop.Syntax == ActiveDirectorySyntax.Bool)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = (SqlBoolean)(string.Compare(sre.Attributes[\"" + prop.Name + "\"][0].ToString(), \"true\", true) == 0);");
				}
				else if (prop.Syntax == ActiveDirectorySyntax.OctetString && prop.RangeUpper == 16)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = GlobalMethods.ConvertDirectoryAttributeByteArrayToGuid(sre.Attributes[\"" + prop.Name + "\"]);");
					sb.AppendLine("\t\t\tif (" + prop.Name.Replace('-', '_') + " == Guid.Empty)");
					sb.AppendLine("\t\t\t{");
					sb.AppendLine("\t\t\t\t" + prop.Name.Replace('-', '_') + " = SqlGuid.Null;");
					sb.AppendLine("\t\t\t}");
				}
				else if (prop.IsSingleValued && prop.Syntax == ActiveDirectorySyntax.Int)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " =  Convert.ToInt32(sre.Attributes[\"" + prop.Name + "\"][0]);");

					if (prop.Name == "userAccountControl")
					{
						sb.AppendLine("\t\t\tADS_UF_SCRIPT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.SCRIPT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_ACCOUNTDISABLE = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.ACCOUNTDISABLE) > 0;");
						sb.AppendLine("\t\t\tADS_UF_HOMEDIR_REQUIRED = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.HOMEDIR_REQUIRED) > 0;");
						sb.AppendLine("\t\t\tADS_UF_LOCKOUT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.LOCKOUT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_PASSWD_NOTREQD = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.PASSWD_NOTREQD) > 0;");
						sb.AppendLine("\t\t\tADS_UF_PASSWD_CANT_CHANGE = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.PASSWD_CANT_CHANGE) > 0;");
						sb.AppendLine("\t\t\tADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.ENCRYPTED_TEXT_PASSWORD_ALLOWED) > 0;");
						sb.AppendLine("\t\t\tADS_UF_TEMP_DUPLICATE_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.TEMP_DUPLICATE_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_NORMAL_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.NORMAL_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_INTERDOMAIN_TRUST_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.INTERDOMAIN_TRUST_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_WORKSTATION_TRUST_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.WORKSTATION_TRUST_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_SERVER_TRUST_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.SERVER_TRUST_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_DONT_EXPIRE_PASSWD = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.DONT_EXPIRE_PASSWD) > 0;");
						sb.AppendLine("\t\t\tADS_UF_MNS_LOGON_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.MNS_LOGON_ACCOUNT) > 0;");
						sb.AppendLine("\t\t\tADS_UF_SMARTCARD_REQUIRED = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.SMARTCARD_REQUIRED) > 0;");
						sb.AppendLine("\t\t\tADS_UF_TRUSTED_FOR_DELEGATION = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.TRUSTED_FOR_DELEGATION) > 0;");
						sb.AppendLine("\t\t\tADS_UF_NOT_DELEGATED = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.NOT_DELEGATED) > 0;");
						sb.AppendLine("\t\t\tADS_UF_USE_DES_KEY_ONLY = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.USE_DES_KEY_ONLY) > 0;");
						sb.AppendLine("\t\t\tADS_UF_DONT_REQ_PREAUTH = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.DONT_REQUIRE_PREAUTH) > 0;");
						sb.AppendLine("\t\t\tADS_UF_PASSWORD_EXPIRED = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.PASSWORD_EXPIRED) > 0;");
						sb.AppendLine("\t\t\tADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION) > 0;");
						sb.AppendLine("\t\t\tADS_UF_PARTIAL_SECRETS_ACCOUNT = ((Ads_User_Flags)sre.Attributes[\"" + prop.Name + "\"][0] & Ads_User_Flags.PARTIAL_SECRETS_ACCOUNT) > 0;");
					}
				}
				else if (prop.Syntax == ActiveDirectorySyntax.Int64)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = Convert.ToInt64(sre.Attributes[\"" + prop.Name + "\"][0]);");
				}
				else if (prop.Syntax == ActiveDirectorySyntax.GeneralizedTime || prop.Syntax == ActiveDirectorySyntax.UtcTime)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = DateTime.ParseExact(sre.Attributes[\"" + prop.Name + "\"][0].ToString(), \"yyyyMMddHHmmss.f'Z'\", CultureInfo.InvariantCulture);");
				}
				else if (prop.Syntax == ActiveDirectorySyntax.OctetString)
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = (byte[])sre.Attributes[\"" + prop.Name + "\"][0];");
				}
				else
				{
					sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = sre.Attributes[\"" + prop.Name + "\"][0].ToString();");
				}
			}
			else //multi-valued attribute
			{
				sb.AppendLine("\t\t\t//TODO:Iterate and Join MVA");
				sb.AppendLine("\t\t\tusing (MemoryStream stream = new MemoryStream())");
				sb.AppendLine("\t\t\t{");
				sb.AppendLine("\t\t\t\tusing (XmlWriter w = XmlWriter.Create(stream))");
				sb.AppendLine("\t\t\t\t{");
				sb.AppendLine("\t\t\t\t\tforeach (DirectoryAttribute da in sre.Attributes[\"" + prop.Name + "\"])");
				sb.AppendLine("\t\t\t\t\t{");
				sb.AppendLine("\t\t\t\t\t\tw.WriteStartElement(\"DirectoryAttribute\");");

				//this puts the attribute data into the xml.  may need logic based on data type.
				sb.AppendLine("\t\t\t\t\t\tw.WriteValue(da);");

				sb.AppendLine("\t\t\t\t\t\tw.WriteEndElement();");
				sb.AppendLine("\t\t\t\t\t}");
				sb.AppendLine();
				sb.AppendLine("\t\t\t\t\t" + prop.Name.Replace('-', '_') + " = new SqlXml(stream);");
				sb.AppendLine("\t\t\t\t}");
				sb.AppendLine("\t\t\t}");
			}
			sb.AppendLine("\t\t}");
			sb.AppendLine("\t\telse");
			sb.AppendLine("\t\t{");
			sb.AppendLine("\t\t\t" + prop.Name.Replace('-', '_') + " = " + getSqlDataType(prop) + ".Null;");
			if (prop.Name == "userAccountControl")
			{
				sb.AppendLine("\t\t\tADS_UF_SCRIPT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_ACCOUNTDISABLE = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_HOMEDIR_REQUIRED = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_LOCKOUT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_PASSWD_NOTREQD = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_PASSWD_CANT_CHANGE = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_ENCRYPTED_TEXT_PWD_ALLOWED = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_TEMP_DUPLICATE_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_NORMAL_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_INTERDOMAIN_TRUST_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_WORKSTATION_TRUST_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_SERVER_TRUST_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_DONT_EXPIRE_PASSWD = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_MNS_LOGON_ACCOUNT = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_SMARTCARD_REQUIRED = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_TRUSTED_FOR_DELEGATION = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_NOT_DELEGATED = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_USE_DES_KEY_ONLY = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_DONT_REQ_PREAUTH = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_PASSWORD_EXPIRED = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_TRUSTED_TO_AUTH_FOR_DELEGATION = SqlBoolean.Null;");
				sb.AppendLine("\t\t\tADS_UF_PARTIAL_SECRETS_ACCOUNT = SqlBoolean.Null;");
			}
			sb.AppendLine("\t\t}");
			sb.AppendLine();

			return sb.ToString();
		}

		private string getDataType(ActiveDirectorySchemaProperty prop)
		{
			if (prop.IsSingleValued)
			{
				switch (prop.Syntax)
				{
					case ActiveDirectorySyntax.DN:
					case ActiveDirectorySyntax.PresentationAddress:
					case ActiveDirectorySyntax.SecurityDescriptor:
						return "NTEXT";
					case ActiveDirectorySyntax.DirectoryString:
					case ActiveDirectorySyntax.PrintableString:
						if (prop.RangeUpper <= 8000)
						{
							return "NVARCHAR(" + prop.RangeUpper + ")";
						}
						else
						{
							return "NVARCHAR(MAX)";
						}
					case ActiveDirectorySyntax.Int:
						return "INT";
					case ActiveDirectorySyntax.Int64:
						return "BIGINT";
					case ActiveDirectorySyntax.Bool:
						return "BIT";
					case ActiveDirectorySyntax.UtcTime:
					case ActiveDirectorySyntax.GeneralizedTime:
						return "DATETIME";
					case ActiveDirectorySyntax.OctetString:
						switch (prop.RangeUpper)
						{
							case 16:
								return "UNIQUEIDENTIFIER";
							case 64:
								return "BINARY";
							default:
								return "BINARY";
						}
					default:
						return "NTEXT";
				}
			}
			return "XML";
		}

		private string getSqlDataType(ActiveDirectorySchemaProperty prop)
		{
			if (prop.IsSingleValued)
			{
				switch (prop.Syntax)
				{
					case ActiveDirectorySyntax.DN:
					case ActiveDirectorySyntax.SecurityDescriptor:
					case ActiveDirectorySyntax.PresentationAddress:
					case ActiveDirectorySyntax.DirectoryString:
					case ActiveDirectorySyntax.PrintableString:
						return "SqlString";
					case ActiveDirectorySyntax.Int:
						return "SqlInt32";
					case ActiveDirectorySyntax.Int64:
						return "SqlInt64";
					case ActiveDirectorySyntax.Bool:
						return "SqlBoolean";
					case ActiveDirectorySyntax.UtcTime:
					case ActiveDirectorySyntax.GeneralizedTime:
						return "SqlDateTime";
					case ActiveDirectorySyntax.OctetString:
						switch (prop.RangeUpper)
						{
							case 16:
								return "SqlGuid";
							case 64:
								return "SqlBinary";
							default:
								return "SqlBinary";
						}
					default:
						return "SqlString";
				}
			}
			return "SqlXml";
		}

		public void Dispose()
		{
			if (_output != null)
			{
				((IDisposable)_output).Dispose();
			}
		}
	}
}
